﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
using Distribution.Data;
using Distribution.General;
using Entities;

namespace Distribution.Data
{
    public class DPole
    {
        #region - Public methods -

        public static DataTable GetPoleDetails ()
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT 'test' test, tb_Poles.PoleId, tb_Poles.Number, COUNT(tb_Meters.MeterId) Meter, tb_Poles.AcquisitionDate Time ");
                query.Append("FROM tb_Poles LEFT JOIN tb_Meters ON tb_Poles.PoleId = tb_Meters.PoleFk ");
                query.Append("GROUP BY tb_Poles.PoleId, tb_Poles.Number, tb_Poles.AcquisitionDate");

                _Da = new SqlCeDataAdapter(query.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void Delete(EPole pole)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole DELETE -

                query.AppendLine("DELETE tb_Poles ");
                query.AppendLine("WHERE PoleId = @PoleId");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                #region - Phases DELETE -

                query = new StringBuilder("DELETE tb_PhasesXPoles WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                #region - Tops UPDATE -

                query = new StringBuilder("DELETE tb_TopsXPoles WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                #region - Image UPDATE -

                query = new StringBuilder("DELETE tb_PoleImages WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_PoleResults WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("DELETE tb_Pole_InspectionResult ");
                query.AppendLine("WHERE PoleFk = @PoleFk");

                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                #endregion

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Save(EPole pole)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();
            
            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole INSERT -

                query.AppendLine("INSERT INTO tb_Poles (PolygonMapFk, PolygonSectionFk, SpecieFk, ClassFk, LengthFk, TreatmentFk, Latitude, Longitude, InspBy, Number, AcquisitionDate, Year, LVisit, GuardsQuantity, PoleTag, GroundWireRepair, Billable, Kv, Circuit, ConductorType, ACSR) VALUES (");
                query.AppendLine("@PolygonMapFk,");
                query.AppendLine("@PolygonSectionFk,");
                query.AppendLine("@SpecieFk,");
                query.AppendLine("@ClassFk,");
                query.AppendLine("@LengthFk,");
                query.AppendLine("@TreatmentFk,");
                query.AppendLine("@Latitude,");
                query.AppendLine("@Longitude,");
                query.AppendLine("@InspBy,");
                query.AppendLine("@Number,");
                query.AppendLine("@AcquisitionDate,");
                query.AppendLine("@Year, @LVisit,");
                query.AppendLine("@GuardsQuantity,"); 
                query.AppendLine("@PoleTag,");
                query.AppendLine("@GroundWireRepair,");
                query.AppendLine("@Billable,");
                query.AppendLine("@Kv,");
                query.AppendLine("@Circuit,");
                query.AppendLine("@ConductorType,");
                query.AppendLine("@ACSR)");


                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                if (pole.Map != null && pole.Map.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, pole.Map.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Section != null && pole.Section.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, pole.Section.Id));
                else 
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, DBNull.Value));
                
                if (pole.Specie != null && pole.Specie.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, pole.Specie.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Class != null && pole.Class.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Length != null && pole.Length.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, pole.Length.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Treatment != null && pole.Treatment.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, pole.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, pole.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.InspBy != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, pole.InspBy));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.Number != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, pole.Number));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.LVisit != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, DBNull.Value));
                
                command.Parameters.Add(Helper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, DateTime.Now));

                if (pole.Year != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Year", ParameterDirection.Input, false, DBNull.Value));

                if (pole.GuardsQuantity != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, pole.GuardsQuantity));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, pole.PoleTag));
                command.Parameters.Add(Helper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, pole.GroundWireRepair));
                command.Parameters.Add(Helper.CreateBitParameter("@Billable", ParameterDirection.Input, false, pole.Billable));

                if (pole.Kv != null && pole.Kv.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@Kv", ParameterDirection.Input, false, pole.Kv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Kv", ParameterDirection.Input, false, DBNull.Value));
                
                command.Parameters.Add(Helper.CreateNVarCharParameter("@Circuit", 100, ParameterDirection.Input, false, pole.Circuit));

                if (pole.ConductorType != null && pole.ConductorType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, pole.ConductorType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, DBNull.Value));


                if (pole.ACSR != null && pole.ACSR.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, pole.ACSR.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, DBNull.Value));
                

                command.ExecuteNonQuery();

                //Gets the last inserted PoleId 
                query = new StringBuilder();
                query.AppendLine("SELECT MAX(PoleId) AS id FROM tb_Poles");

                command = new SqlCeCommand(query.ToString(), (SqlCeConnection) tx.Connection, tx);
                command.CommandType = CommandType.Text;
                Int32 lastId = (int)command.ExecuteScalar();

                #endregion

                #region - Phases INSERT -

                foreach (ELookUp phase in pole.Phases)
                {
                    query = new StringBuilder("INSERT INTO tb_PhasesXPoles (PoleFk, PhaseFk) VALUES (");
                    query.AppendLine("@PoleFk, @PhaseFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, phase.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - Tops INSERT -

                foreach (ELookUp top in pole.Tops)
                {
                    query = new StringBuilder("INSERT INTO tb_TopsXPoles (PoleFk, TopFk) VALUES (");
                    query.AppendLine("@PoleFk, @TopFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, top.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - Image INSERT -

                int imagesCount = 0;

                foreach (EImage image in pole.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleImages (PoleFk, FileName) VALUES (");
                    query.AppendLine("@PoleFk, @FileName)");

                    image.GenerateNewName(lastId.ToString() + imagesCount);

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));
                    command.ExecuteNonQuery();
                    
                    imagesCount++;
                }

                #endregion

                #region - Notes INSERT -

                foreach (ELookUp note in pole.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleResults (PoleFk, ResultTypeFk) VALUES (");
                    query.AppendLine("@PoleFk, @ResultTypeFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, note.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("INSERT INTO tb_Pole_InspectionResult (PoleFk, InspectionTypeFk, Comments, isElectrical, ResultTypeFk) VALUES (");
                query.AppendLine("@PoleFk, @InspectionTypeFk, @Comments, @isElectrical, @ResultTypeFk)");

                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                if (pole.InspectionType != null && pole.InspectionType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, pole.InspectionType.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.ResultType != null && pole.ResultType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, pole.ResultType.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, pole.Comments));
                command.Parameters.Add(Helper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, pole.isElectrical));

                command.ExecuteNonQuery();

                #endregion

                tx.Commit();

                pole.Id = lastId;
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Update(EPole pole)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole INSERT -

                query.AppendLine("Update tb_Poles SET ");
                query.AppendLine("PolygonMapFk = @PolygonMapFk,");
                query.AppendLine("PolygonSectionFk = @PolygonSectionFk,");
                query.AppendLine("SpecieFk = @SpecieFk,");
                query.AppendLine("ClassFk = @ClassFk,");
                query.AppendLine("LengthFk = @LengthFk,");
                query.AppendLine("TreatmentFk = @TreatmentFk,");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("InspBy = @InspBy,");
                query.AppendLine("Number = @Number,");
                query.AppendLine("AcquisitionDate = @AcquisitionDate,");
                query.AppendLine("Year = @Year, LVisit = @LVisit, ");
                query.AppendLine("GuardsQuantity = @GuardsQuantity,");
                query.AppendLine("PoleTag = @PoleTag,");
                query.AppendLine("GroundWireRepair = @GroundWireRepair,");
                query.AppendLine("Billable = @Billable,");
                query.AppendLine("Kv = @Kv,");
                query.AppendLine("Circuit = @Circuit,");
                query.AppendLine("ConductorType = @ConductorType,");
                query.AppendLine("ACSR = @ACSR");
                query.AppendLine("WHERE PoleId = @PoleId");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                if (pole.Map != null && pole.Map.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, pole.Map.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Section != null && pole.Section.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, pole.Section.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Specie != null && pole.Specie.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, pole.Specie.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Class != null && pole.Class.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Length != null && pole.Length.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, pole.Length.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Treatment != null && pole.Treatment.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, pole.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, pole.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.InspBy != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, pole.InspBy));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.Number != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, pole.Number));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.LVisit != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, DateTime.Now));

                if (pole.Year != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Year", ParameterDirection.Input, false, DBNull.Value));

                if (pole.GuardsQuantity != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, pole.GuardsQuantity));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, pole.PoleTag));
                command.Parameters.Add(Helper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, pole.GroundWireRepair));
                command.Parameters.Add(Helper.CreateBitParameter("@Billable", ParameterDirection.Input, false, pole.Billable));

                if (pole.Kv != null && pole.Kv.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@Kv", ParameterDirection.Input, false, pole.Kv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Kv", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateNVarCharParameter("@Circuit", 100, ParameterDirection.Input, false, pole.Circuit));

                if (pole.ConductorType != null && pole.ConductorType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, pole.ConductorType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, DBNull.Value));


                if (pole.ACSR != null && pole.ACSR.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, pole.ACSR.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, DBNull.Value));
                

                command.ExecuteNonQuery();

                //Int32 lastId = pole.Id;

                #endregion

                #region - Phases UPDATE -

                query = new StringBuilder("DELETE tb_PhasesXPoles WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                foreach (ELookUp phase in pole.Phases)
                {
                    query = new StringBuilder("INSERT INTO tb_PhasesXPoles (PoleFk, PhaseFk) VALUES (");
                    query.AppendLine("@PoleFk, @PhaseFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, phase.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - Tops UPDATE -

                query = new StringBuilder("DELETE tb_TopsXPoles WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                foreach (ELookUp top in pole.Tops)
                {
                    query = new StringBuilder("INSERT INTO tb_TopsXPoles (PoleFk, TopFk) VALUES (");
                    query.AppendLine("@PoleFk, @TopFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, top.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - Image UPDATE -

                query = new StringBuilder("DELETE tb_PoleImages WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                int imagesCount = 0;

                foreach (EImage image in pole.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleImages (PoleFk, FileName) VALUES (");
                    query.AppendLine("@PoleFk, @FileName)");

                    image.GenerateNewName(pole.Id.ToString() + imagesCount);
                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));

                    command.ExecuteNonQuery();
                    imagesCount++;
                }

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_PoleResults WHERE PoleFk = @PoleFk");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.ExecuteNonQuery();

                foreach (ELookUp note in pole.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleResults (PoleFk, ResultTypeFk) VALUES (");
                    query.AppendLine("@PoleFk, @ResultTypeFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, note.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("UPDATE tb_Pole_InspectionResult ");
                query.AppendLine("SET PoleFk = @PoleFk, ");
                query.AppendLine("InspectionTypeFk = @InspectionTypeFk, ");
                query.AppendLine("Comments = @Comments, ");
                query.AppendLine("isElectrical = @isElectrical, "); 
                query.AppendLine("ResultTypeFk = @ResultTypeFk ");
                query.AppendLine("WHERE PoleFk = @PoleFk");

                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                if (pole.InspectionType != null && pole.InspectionType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, pole.InspectionType.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.ResultType != null && pole.ResultType.Id != Int32.MinValue)
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, pole.ResultType.Value));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, pole.Comments));
                command.Parameters.Add(Helper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, pole.isElectrical));

                command.ExecuteNonQuery();

                #endregion

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static EPole GetBasicDataById(EPole pole)
        {
            SqlCeConnection _Con = Helper.GetConnection();
            StringBuilder query = new StringBuilder();

            query.Append("SELECT PoleId, PolygonMapFk, PolygonSectionFk, SpecieFk, ClassFk, LengthFk, TreatmentFk, Latitude, Longitude, InspBy, Number, AcquisitionDate, Year, LVisit, GuardsQuantity, PoleTag, GroundWireRepair, Billable, Kv, Circuit, ConductorType, ACSR ");
            query.Append("FROM tb_Poles ");
            query.Append("WHERE PoleId = @PoleId ");

            SqlCeCommand command = new SqlCeCommand(query.ToString(), _Con);
            command.CommandType = CommandType.Text;
            command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

            SqlCeDataReader dr = command.ExecuteReader();
            EPole loadedPole = new EPole();

            if (dr.Read())
            {
                loadedPole.Id = dr.GetInt32(dr.GetOrdinal("PoleId"));

                if (!dr.IsDBNull(dr.GetOrdinal("PolygonMapFk")))
                    loadedPole.Map =
                        DPolygon.GetByIdType(ConvertMobileHelper.ToInt32(dr.GetInt32(dr.GetOrdinal("PolygonMapFk"))),
                                             PolygonTypeEnum.Map);

                if (!dr.IsDBNull(dr.GetOrdinal("PolygonSectionFk")))
                    loadedPole.Section =
                        DPolygon.GetByIdType(dr.GetInt32(dr.GetOrdinal("PolygonSectionFk")), PolygonTypeEnum.Section);

                if (!dr.IsDBNull(dr.GetOrdinal("SpecieFk")))
                    loadedPole.Specie = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("SpecieFk")), LookUpType.Species);

                if (!dr.IsDBNull(dr.GetOrdinal("ClassFk")))
                    loadedPole.Class = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("ClassFk")), LookUpType.Class);

                if (!dr.IsDBNull(dr.GetOrdinal("LengthFk")))
                    loadedPole.Length = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("LengthFk")), LookUpType.Length);

                if (!dr.IsDBNull(dr.GetOrdinal("TreatmentFk")))
                    loadedPole.Treatment = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("TreatmentFk")), LookUpType.Treatment);

                if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                    loadedPole.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                    loadedPole.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                if (!dr.IsDBNull(dr.GetOrdinal("InspBy")))
                    loadedPole.InspBy = dr.GetString(dr.GetOrdinal("InspBy"));

                if (!dr.IsDBNull(dr.GetOrdinal("Number")))
                    loadedPole.Number = dr.GetString(dr.GetOrdinal("Number"));

                if (!dr.IsDBNull(dr.GetOrdinal("AcquisitionDate")))
                    loadedPole.AcquisitionDate = dr.GetDateTime(dr.GetOrdinal("AcquisitionDate"));

                if (!dr.IsDBNull(dr.GetOrdinal("Year")))
                    loadedPole.Year = dr.GetInt32(dr.GetOrdinal("Year"));

                if (!dr.IsDBNull(dr.GetOrdinal("LVisit")))
                    loadedPole.LVisit = dr.GetDateTime(dr.GetOrdinal("LVisit"));

                if (!dr.IsDBNull(dr.GetOrdinal("GuardsQuantity")))
                    loadedPole.GuardsQuantity = dr.GetInt32(dr.GetOrdinal("GuardsQuantity"));

                if (!dr.IsDBNull(dr.GetOrdinal("PoleTag")))
                    loadedPole.PoleTag = dr.GetBoolean(dr.GetOrdinal("PoleTag"));

                if (!dr.IsDBNull(dr.GetOrdinal("GroundWireRepair")))
                    loadedPole.GroundWireRepair = dr.GetBoolean(dr.GetOrdinal("GroundWireRepair"));

                if (!dr.IsDBNull(dr.GetOrdinal("Billable")))
                    loadedPole.Billable = dr.GetBoolean(dr.GetOrdinal("Billable"));

                if (!dr.IsDBNull(dr.GetOrdinal("Kv")))
                    loadedPole.Kv = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Kv")), TransformerLookUpType.Kv);

                if (!dr.IsDBNull(dr.GetOrdinal("ConductorType")))
                    loadedPole.ConductorType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("ConductorType")), TransformerLookUpType.ConductorType);

                if (!dr.IsDBNull(dr.GetOrdinal("ACSR")))
                    loadedPole.ACSR = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("ACSR")), TransformerLookUpType.ACSR);

                if (!dr.IsDBNull(dr.GetOrdinal("Circuit")))
                    loadedPole.Circuit = dr.GetString(dr.GetOrdinal("Circuit"));
            }

            return loadedPole;
        }

        public static EPole GetById(EPole pole)
        {
            SqlCeConnection _Con = Helper.GetConnection();
            StringBuilder query = new StringBuilder();

            query.Append("SELECT PoleId, PolygonMapFk, PolygonSectionFk, SpecieFk, ClassFk, LengthFk, TreatmentFk, Latitude, Longitude, InspBy, Number, AcquisitionDate, Year, LVisit, GuardsQuantity, PoleTag, GroundWireRepair, Billable, Kv, Circuit, ConductorType, ACSR  ");
            query.Append("FROM tb_Poles ");
            query.Append("WHERE PoleId = @PoleId ");

            SqlCeCommand command = new SqlCeCommand(query.ToString(), _Con);
            command.CommandType = CommandType.Text;
            command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

            SqlCeDataReader dr = command.ExecuteReader();
            EPole loadedPole = new EPole();

            if (dr.Read())
            {
                loadedPole.Id = dr.GetInt32(dr.GetOrdinal("PoleId"));

                if (!dr.IsDBNull(dr.GetOrdinal("PolygonMapFk")))
                    loadedPole.Map = DPolygon.GetByIdType(ConvertMobileHelper.ToInt32(dr.GetInt32(dr.GetOrdinal("PolygonMapFk"))), PolygonTypeEnum.Map);

                if (!dr.IsDBNull(dr.GetOrdinal("PolygonSectionFk")))
                    loadedPole.Section = DPolygon.GetByIdType(dr.GetInt32(dr.GetOrdinal("PolygonSectionFk")), PolygonTypeEnum.Section);

                if (!dr.IsDBNull(dr.GetOrdinal("SpecieFk")))
                    loadedPole.Specie = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("SpecieFk")), LookUpType.Species);

                if (!dr.IsDBNull(dr.GetOrdinal("ClassFk")))
                    loadedPole.Class = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("ClassFk")), LookUpType.Class);

                if (!dr.IsDBNull(dr.GetOrdinal("LengthFk")))
                    loadedPole.Length = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("LengthFk")), LookUpType.Length);

                if (!dr.IsDBNull(dr.GetOrdinal("TreatmentFk")))
                    loadedPole.Treatment = DLookUp.GetByIdType(dr.GetInt32(dr.GetOrdinal("TreatmentFk")), LookUpType.Treatment);

                if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                    loadedPole.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                    loadedPole.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                if (!dr.IsDBNull(dr.GetOrdinal("InspBy")))
                    loadedPole.InspBy = dr.GetString(dr.GetOrdinal("InspBy"));

                if (!dr.IsDBNull(dr.GetOrdinal("Number")))
                    loadedPole.Number = dr.GetString(dr.GetOrdinal("Number"));

                if (!dr.IsDBNull(dr.GetOrdinal("AcquisitionDate")))
                    loadedPole.AcquisitionDate = dr.GetDateTime(dr.GetOrdinal("AcquisitionDate"));

                if (!dr.IsDBNull(dr.GetOrdinal("Year")))
                    loadedPole.Year = dr.GetInt32(dr.GetOrdinal("Year"));

                if (!dr.IsDBNull(dr.GetOrdinal("LVisit")))
                    loadedPole.LVisit = dr.GetDateTime(dr.GetOrdinal("LVisit"));
                
                if (!dr.IsDBNull(dr.GetOrdinal("GuardsQuantity")))
                    loadedPole.GuardsQuantity = dr.GetInt32(dr.GetOrdinal("GuardsQuantity"));

                if (!dr.IsDBNull(dr.GetOrdinal("PoleTag")))
                    loadedPole.PoleTag = dr.GetBoolean(dr.GetOrdinal("PoleTag"));

                if (!dr.IsDBNull(dr.GetOrdinal("GroundWireRepair")))
                    loadedPole.GroundWireRepair = dr.GetBoolean(dr.GetOrdinal("GroundWireRepair"));

                if (!dr.IsDBNull(dr.GetOrdinal("Billable")))
                    loadedPole.Billable = dr.GetBoolean(dr.GetOrdinal("Billable"));

                if (!dr.IsDBNull(dr.GetOrdinal("Kv")))
                    loadedPole.Kv = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Kv")), TransformerLookUpType.Kv);

                if (!dr.IsDBNull(dr.GetOrdinal("Circuit")))
                    loadedPole.Circuit = dr.GetString(dr.GetOrdinal("Circuit"));

                if (!dr.IsDBNull(dr.GetOrdinal("ACSR")))
                    loadedPole.ACSR = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("ACSR")), TransformerLookUpType.ACSR);

                if (!dr.IsDBNull(dr.GetOrdinal("ConductorType")))
                    loadedPole.ConductorType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("ConductorType")), TransformerLookUpType.ConductorType);

                //Load pole's images
                query = new StringBuilder("SELECT ImageId, FileName ");
                query.Append("FROM tb_PoleImages ");
                query.Append("WHERE PoleFk = @PoleId ");
                command = new SqlCeCommand(query.ToString(), _Con);
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                loadedPole.Images = new List<EImage>();
                SqlCeDataReader drImages = command.ExecuteReader();

                while (drImages.Read())
                {
                    EImage image = new EImage();

                    image.ImageType = ImageType.Pole;
                    image.Id = drImages.GetInt32(drImages.GetOrdinal("ImageId"));
                    image.FileName = drImages.GetString(drImages.GetOrdinal("FileName"));

                    loadedPole.Images.Add(image);
                }

                //Load pole's phases
                query = new StringBuilder("SELECT PhaseFk ");
                query.Append("FROM tb_PhasesXPoles ");
                query.Append("WHERE PoleFk = @PoleId ");
                command = new SqlCeCommand(query.ToString(), _Con);
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                loadedPole.Phases = new List<ELookUp>();
                SqlCeDataReader drPoles = command.ExecuteReader();

                while (drPoles.Read())
                {
                    ELookUp phase = DLookUp.GetByIdType(drPoles.GetInt32(drPoles.GetOrdinal("PhaseFk")), LookUpType.Phase);
                    loadedPole.Phases.Add(phase);
                }

                //Load pole's tops
                query = new StringBuilder("SELECT TopFk ");
                query.Append("FROM tb_TopsXPoles ");
                query.Append("WHERE PoleFk = @PoleId ");
                command = new SqlCeCommand(query.ToString(), _Con);
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                loadedPole.Tops = new List<ELookUp>();
                SqlCeDataReader drTops = command.ExecuteReader();

                while (drTops.Read())
                {
                    ELookUp top = DLookUp.GetByIdType(drTops.GetInt32(drTops.GetOrdinal("TopFk")), LookUpType.Top);
                    loadedPole.Tops.Add(top);
                }

                //Load pole's result types
                query = new StringBuilder("SELECT ResultTypeFk ");
                query.Append("FROM tb_PoleResults ");
                query.Append("WHERE PoleFk = @PoleId ");
                command = new SqlCeCommand(query.ToString(), _Con);
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                loadedPole.Notes = new List<ELookUp>();
                SqlCeDataReader drNotes = command.ExecuteReader();

                while (drNotes.Read())
                {
                    ELookUp note = DLookUp.GetByIdType(drNotes.GetInt32(drNotes.GetOrdinal("ResultTypeFk")), LookUpType.InspectionResult);
                    loadedPole.Notes.Add(note);
                }

                //Load pole's inspection result
                query = new StringBuilder("SELECT InspectionTypeFk, Comments, isElectrical, PoleFk, ResultTypeFk ");
                query.Append("FROM tb_Pole_InspectionResult ");
                query.Append("WHERE PoleFk = @PoleId ");
                command = new SqlCeCommand(query.ToString(), _Con);
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                SqlCeDataReader drInspectionResult = command.ExecuteReader();

                if (drInspectionResult.Read())
                {
                    if (!drInspectionResult.IsDBNull(drInspectionResult.GetOrdinal("InspectionTypeFk")))
                        loadedPole.InspectionType = DLookUp.GetByIdType(drInspectionResult.GetInt32(drInspectionResult.GetOrdinal("InspectionTypeFk")), LookUpType.InspectionType);

                    if (!drInspectionResult.IsDBNull(drInspectionResult.GetOrdinal("ResultTypeFk")))
                        loadedPole.ResultType = DLookUp.GetByIdType(drInspectionResult.GetInt32(drInspectionResult.GetOrdinal("ResultTypeFk")), LookUpType.ResultType);

                    loadedPole.isElectrical = drInspectionResult.GetBoolean(drInspectionResult.GetOrdinal("isElectrical"));

                    if (!drInspectionResult.IsDBNull(drInspectionResult.GetOrdinal("Comments")))
                        loadedPole.Comments = drInspectionResult.GetString(drInspectionResult.GetOrdinal("Comments"));
                }
            }

            return loadedPole;
        }

        #endregion
    }
}